//
//  ContactsDateBase.m
//  SIPphone
//
//  Created by wrt on 13-11-27.
//  Copyright (c) 2013年 wrtsoft. All rights reserved.
//  联系人数据库单例



#import "ContactsDateBase.h"

//数据库对象的单例
static ContactsDateBase *shareContactsDataBase = nil;

@implementation ContactsDateBase
{
    //从数据库中读出来的暂存的字符串
    NSString *tempUserName;
    NSString *tempDisplayName;
    NSString *tempMarkName;
    NSString *tempIpPort;
    NSString *tempOnlinestatus;
    NSString *tempIsFriend;
}

+ (ContactsDateBase *)shareContactsDataBase
{
    @synchronized(self)
    {
        if (shareContactsDataBase == nil)
        {
            shareContactsDataBase = [[self alloc] init];
        }
    }
    return shareContactsDataBase;
}

//切换表名
- (void)changeTableName:(NSString *)userName
{
    self.tableNameForUser = [[NSString alloc] init];
    self.tableNameForUser = [NSString stringWithFormat:@"contactsTable_%@",userName];
}

//创建数据库
- (void)creatDB
{
    //在一个新线程中进行数据库存储操作
    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //创建表
            char *error;
            NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (ID INTEGER PRIMARY KEY AUTOINCREMENT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT)",self.tableNameForUser ,CONTACTS_USERNAME ,CONTACTS_DISPLAYNAME ,CONTACTS_MARKNAME ,CONTACTS_IPPORT ,CONTACTS_ONLIENSTATUS ,CONTACTS_ISFRIEND];
            //运行SQL语句
            if(sqlite3_exec(db, [createSQL UTF8String], NULL, NULL, &error) != SQLITE_OK
               )
            {
                NSLog(@"建表失败！%s",error);
            }
            //关闭数据库
            sqlite3_close(db);
        }
//    });
}

//读取数据库
- (void)readDB
{
    dispatch_async(dispatch_get_main_queue(),^{
    //将所需要存数据的数组初始化
    self.userNameArray = [[NSMutableArray alloc] init];
    self.displayNameArray = [[NSMutableArray alloc] init];
    self.markNameArray = [[NSMutableArray alloc] init];
    self.ipPortArray = [[NSMutableArray alloc] init];
    self.onlineStatusArray = [[NSMutableArray alloc] init];
    self.isFriendArray = [[NSMutableArray alloc] init];
    
    //数据库先执行一遍读
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM %@",self.tableNameForUser];
        
        sqlite3_stmt *statement;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            //执行
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                char *text1 = (char *)sqlite3_column_text(statement, 1);
                tempUserName = [[NSString alloc] initWithUTF8String:text1];
                [self.userNameArray addObject:tempUserName];
                
                char *text2 = (char *)sqlite3_column_text(statement, 2);
                tempDisplayName = [[NSString alloc] initWithUTF8String:text2];
                if (!(tempDisplayName.length > 0))
                {
                    tempDisplayName = tempUserName;
                }
                [self.displayNameArray addObject:tempDisplayName];
                
                char *text3 = (char *)sqlite3_column_text(statement, 3);
                tempMarkName = [[NSString alloc] initWithUTF8String:text3];
                if (!(tempMarkName.length > 0))
                {
                    tempMarkName = tempDisplayName;
                }
                [self.markNameArray addObject:tempMarkName];
                
                char *text4 = (char *)sqlite3_column_text(statement, 4);
                tempIpPort = [[NSString alloc] initWithUTF8String:text4];
                [self.ipPortArray addObject:tempIpPort];
                
                char *text5 = (char *)sqlite3_column_text(statement, 5);
                tempOnlinestatus = [[NSString alloc] initWithUTF8String:text5];
                [self.onlineStatusArray addObject:tempOnlinestatus];
                
                char *text6 = (char *)sqlite3_column_text(statement, 6);
                tempIsFriend = [[NSString alloc] initWithUTF8String:text6];
                [self.isFriendArray addObject:tempIsFriend];
            }
        }
        //释放状态资源
        sqlite3_finalize(statement);
        //关闭数据库
        sqlite3_close(db);
    }
    });
}

//保存数据库
- (void)saveFriendIntoDBWithUserName:(NSString *)userName andMarkName:(NSString *)markName andIp:(NSString *)ip
{
    //在一个新线程中进行数据库存储操作
//    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
        //数据库写操作
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
            NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@,%@,%@) VALUES(?,?,?,?,?,?)",self.tableNameForUser,CONTACTS_USERNAME,CONTACTS_DISPLAYNAME,CONTACTS_MARKNAME,CONTACTS_IPPORT,CONTACTS_ONLIENSTATUS,CONTACTS_ISFRIEND];
            sqlite3_stmt *statment;
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
            {
                //绑定参数
                sqlite3_bind_text(statment, 1, [userName UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 2, [@"" UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 3, [markName UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 4, [ip UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 5, [@"离线" UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 6, [@"陌生人" UTF8String], -1, NULL);
                //执行插入
                if(sqlite3_step(statment) != SQLITE_DONE)
                {
                    NSLog(@"插入失败");
                }
            }
            
            //释放状态资源
            sqlite3_finalize(statment);
            //关闭数据库
            sqlite3_close(db);
        }
        [self readDB];
//    });
}

//保存数据库(带昵称情况下)
- (void)saveFriendIntoDBWithUserName:(NSString *)userName andMarkName:(NSString *)markName andIp:(NSString *)ip andDisplayName:(NSString *)displayName
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@,%@,%@) VALUES(?,?,?,?,?,?)",self.tableNameForUser,CONTACTS_USERNAME,CONTACTS_DISPLAYNAME,CONTACTS_MARKNAME,CONTACTS_IPPORT,CONTACTS_ONLIENSTATUS,CONTACTS_ISFRIEND];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            //绑定参数
            sqlite3_bind_text(statment, 1, [userName UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 2, [displayName UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 3, [markName UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 4, [ip UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 5, [@"离线" UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 6, [@"好友" UTF8String], -1, NULL);
            //执行插入
            if(sqlite3_step(statment) != SQLITE_DONE)
            {
                NSLog(@"插入失败");
            }
        }
        
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    [self readDB];
}

//保存数据库(好友)
- (void)saveAcceptFriendIntoDBWithUserName:(NSString *)userName andMarkName:(NSString *)markName andIp:(NSString *)ip
{
    //在一个新线程中进行数据库存储操作
    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
        //数据库写操作
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
            NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@,%@,%@) VALUES(?,?,?,?,?,?)",self.tableNameForUser,CONTACTS_USERNAME,CONTACTS_DISPLAYNAME,CONTACTS_MARKNAME,CONTACTS_IPPORT,CONTACTS_ONLIENSTATUS,CONTACTS_ISFRIEND];
            sqlite3_stmt *statment;
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
            {
                //绑定参数
                sqlite3_bind_text(statment, 1, [userName UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 2, [@"" UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 3, [markName UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 4, [ip UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 5, [@"离线" UTF8String], -1, NULL);
                sqlite3_bind_text(statment, 6, [@"好友" UTF8String], -1, NULL);
                //执行插入
                if(sqlite3_step(statment) != SQLITE_DONE)
                {
                    NSLog(@"插入失败");
                }
            }
            
            //释放状态资源
            sqlite3_finalize(statment);
            //关闭数据库
            sqlite3_close(db);
        }
        [self readDB];
//    });
}

//数据库删除操作
- (void)deleteContactsBDWithUserName:(NSString *)userName andIpPort:(NSString *)ip
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,CONTACTS_USERNAME,userName,CONTACTS_IPPORT,ip];
        char *errorMsg;
        if (sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
    [self readDB];
}

//刷新数据库操作
- (void)updateDBWithUserName:(NSString *)userName andIp:(NSString *)ip andStatus:(NSString *)status
{
    //在一个新线程中进行数据库存储操作
    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
    
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
        
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
            NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@',%@ = '%@' WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,CONTACTS_ONLIENSTATUS,status,CONTACTS_ISFRIEND,@"好友",CONTACTS_USERNAME,userName,CONTACTS_IPPORT,ip];
            sqlite3_stmt *statment;
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
            {
                //执行更新
                if(sqlite3_step(statment) != SQLITE_DONE)
                {
                    NSLog(@"更新失败");
                }
            }
            
            //释放状态资源
            sqlite3_finalize(statment);
            //关闭数据库
            sqlite3_close(db);
        }
        
        [self readDB];
//    });
}
//刷新数据库操作(更新昵称)
- (void)updateDBWithUserName:(NSString *)userName andIp:(NSString *)ip andStatus:(NSString *)status andDisplayName:(NSString *)displayname
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@',%@ = '%@', %@ = '%@' WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,CONTACTS_ONLIENSTATUS,status,CONTACTS_ISFRIEND,@"好友",CONTACTS_DISPLAYNAME,displayname,CONTACTS_USERNAME,userName,CONTACTS_IPPORT,ip];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            //执行更新
            if(sqlite3_step(statment) != SQLITE_DONE)
            {
                NSLog(@"更新失败");
            }
        }
        
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    
    [self readDB];
}


//刷新数据库操作
- (void)updateDBForMarkNameWithUserName:(NSString *)userName andIp:(NSString *)ip andNewMarkName:(NSString *)markName
{
    //在一个新线程中进行数据库存储操作
    //查找一个可用的全局队列
//    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
//    //将要做的操作放入队列
//    dispatch_async(queue,^{
    
        NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *myDocPath = myPaths[0];
        NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
        
        //打开数据库
        if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
        {
            //关闭数据库
            sqlite3_close(db);
        }
        else
        {
            //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
            NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@' AND %@ = '%@'",self.tableNameForUser,CONTACTS_MARKNAME,markName,CONTACTS_USERNAME,userName,CONTACTS_IPPORT,ip];
            sqlite3_stmt *statment;
            //预处理
            if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
            {
                //执行更新
                if(sqlite3_step(statment) != SQLITE_DONE)
                {
                    NSLog(@"更新失败");
                }
            }
            
            //释放状态资源
            sqlite3_finalize(statment);
            //关闭数据库
            sqlite3_close(db);
        }
        
        [self readDB];
//    });
}

//获取是否为好友的
- (int)isExistAndIsFriendWith:(NSString *)userName andip:(NSString *)ip
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"contacts.db"];
    
    int i = 0;
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ WHERE %@ = '%@' AND %@ = '%@' AND %@ = '%@'",self.tableNameForUser,CONTACTS_USERNAME,userName,CONTACTS_IPPORT,ip,CONTACTS_ISFRIEND,@"好友"];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statment) == SQLITE_ROW)
            {
                i = sqlite3_column_int(statment, 0);
            }
        }
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    return i;
}

@end
